package com.yupi.springbootinit.utils;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.yupi.springbootinit.common.ErrorCode;
import com.yupi.springbootinit.exception.BusinessException;

import java.util.ArrayList;
import java.util.List;

// SqlGeneratorUtils.java
public class SqlGeneratorUtils {
    
    public static String generateCreateTableSQL(Long chartId, String csvData) {
        if (chartId == null || chartId <= 0) {
            throw new IllegalArgumentException("chartId不能为空且必须大于0");
        }
        
        if (StringUtils.isBlank(csvData)) {
            throw new IllegalArgumentException("csvData不能为空");
        }
        
        String tableName = "chart_" + chartId;
        
        String[] lines = csvData.split("\n");
        if (lines.length == 0) {
            throw new IllegalArgumentException("CSV数据格式错误");
        }
        
        // 处理表头，确保列名有效
        String[] headers = lines[0].split(",");
        List<String> validHeaders = new ArrayList<>();
        
        for (int i = 0; i < headers.length; i++) {
            String columnName = headers[i].trim().replaceAll("^\"|\"$", ""); // 去除可能的引号
            
            // 确保列名不为空
            if (StringUtils.isBlank(columnName)) {
                columnName = "column_" + (i + 1);
            }
            
            // 移除非法字符，只保留字母、数字、下划线和中文字符
            columnName = columnName.replaceAll("[^a-zA-Z0-9_\\u4e00-\\u9fa5]", "_");
            
            // 确保不以数字开头
            if (columnName.length() > 0 && Character.isDigit(columnName.charAt(0))) {
                columnName = "col_" + columnName;
            }
            
            // 如果处理后仍然为空或不合法，则使用默认名称
            if (StringUtils.isBlank(columnName)) {
                columnName = "column_" + (i + 1);
            }
            
            validHeaders.add(columnName);
        }
        
        // 构建CREATE TABLE语句
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
        sql.append("id BIGINT PRIMARY KEY AUTO_INCREMENT, ");
        
        for (int i = 0; i < validHeaders.size(); i++) {
            sql.append(validHeaders.get(i)).append(" TEXT");
            if (i < validHeaders.size() - 1) {
                sql.append(", ");
            }
        }
        
        sql.append(");");
        
        return sql.toString();
    }
    
    // 新增方法：生成INSERT语句
    public static List<String> generateInsertSQLList(Long chartId, String csvData) {
        if (chartId == null || chartId <= 0) {
            throw new IllegalArgumentException("chartId不能为空且必须大于0");
        }
        
        if (StringUtils.isBlank(csvData)) {
            throw new IllegalArgumentException("csvData不能为空");
        }
        
        String tableName = "chart_" + chartId;
        
        String[] lines = csvData.split("\n");
        if (lines.length == 0) {
            throw new IllegalArgumentException("CSV数据格式错误");
        }
        
        // 处理表头，确保列名有效
        String[] headers = lines[0].split(",");
        List<String> validHeaders = new ArrayList<>();
        
        for (int i = 0; i < headers.length; i++) {
            String columnName = headers[i].trim().replaceAll("^\"|\"$", ""); // 去除可能的引号
            
            // 确保列名不为空
            if (StringUtils.isBlank(columnName)) {
                columnName = "column_" + (i + 1);
            }
            
            // 移除非法字符，只保留字母、数字、下划线和中文字符
            columnName = columnName.replaceAll("[^a-zA-Z0-9_\\u4e00-\\u9fa5]", "_");
            
            // 确保不以数字开头
            if (columnName.length() > 0 && Character.isDigit(columnName.charAt(0))) {
                columnName = "col_" + columnName;
            }
            
            // 如果处理后仍然为空或不合法，则使用默认名称
            if (StringUtils.isBlank(columnName)) {
                columnName = "column_" + (i + 1);
            }
            
            validHeaders.add(columnName);
        }
        
        // 构建INSERT语句列表
        List<String> insertSqlList = new ArrayList<>();
        
        for (int i = 1; i < lines.length; i++) {
            String[] values = lines[i].split(",", -1); // 使用-1限制以保留空值
            StringBuilder insertSql = new StringBuilder();
            insertSql.append("INSERT INTO ").append(tableName).append(" (");
            
            for (int j = 0; j < validHeaders.size(); j++) {
                insertSql.append(validHeaders.get(j));
                if (j < validHeaders.size() - 1) {
                    insertSql.append(", ");
                }
            }
            
            insertSql.append(") VALUES (");
            
            for (int j = 0; j < values.length && j < validHeaders.size(); j++) {
                String value = values[j].trim();
                // 处理CSV中的引号
                if (value.startsWith("\"") && value.endsWith("\"")) {
                    value = value.substring(1, value.length() - 1);
                    value = value.replace("\"\"", "\""); // 处理转义引号
                }
                
                insertSql.append("'").append(value.replace("'", "''")).append("'"); // 转义单引号
                if (j < validHeaders.size() - 1) {
                    insertSql.append(", ");
                }
            }
            
            // 如果值的数量少于列数，用空值填充
            for (int j = values.length; j < validHeaders.size(); j++) {
                insertSql.append("''");
                if (j < validHeaders.size() - 1) {
                    insertSql.append(", ");
                }
            }
            
            insertSql.append(");");
            insertSqlList.add(insertSql.toString());
        }
        
        return insertSqlList;
    }
}